package com.crayon2f.test;

import lombok.SneakyThrows;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.FileInputStream;
import java.util.HashMap;
import java.util.Map;
import java.util.Objects;

/**
 * Created by feifan.gou@gmail.com on 2021/3/26 16:57.
 */
public class UpdateDeviceEnName {

    private static final String EXCEL_PATH = "C:\\Users\\goufeifan\\Desktop\\device_en_name.xlsx";

    @Test
    @SneakyThrows
    public void test() {

        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(EXCEL_PATH));
        XSSFSheet sheet = workbook.getSheet("Sheet2");
        Map<String, Long> nameIdMap = new HashMap<>();
        sheet.forEach(row -> {
            Cell cell = row.getCell(0);
            if (Objects.isNull(cell)) {
                return;
            }
            String id = StringUtils.trimToEmpty(cell.getStringCellValue());
            if (id.contains("'")) {
                id = id.substring(1);
            }
            cell = row.getCell(1);
            cell.setCellType(CellType.STRING);
            nameIdMap.put(cell.getStringCellValue(), Long.valueOf(id));
        });
        sheet = workbook.getSheet("Sheet1");
        Map<String, String> nameEnMap = new HashMap<>();
        sheet.forEach(row -> {
            Cell cell = row.getCell(0);
            if (Objects.nonNull(cell)) {
                String name = StringUtils.trimToEmpty(cell.getStringCellValue());
                cell = row.getCell(1);
                if (Objects.nonNull(cell)) {
                    String enName = StringUtils.trimToEmpty(cell.getStringCellValue());
                    nameEnMap.put(name, enName);
                }
            }
        });
        StringBuilder builder = new StringBuilder();
        int[] index = {0};
        nameEnMap.forEach((name,en) -> {
            if (nameIdMap.containsKey(name)) {
                builder.append(String.format("update device_base_info set en_name = '%s' where id = %d;", en, nameIdMap.get(name)));
                index[0]++;
            } else {
                System.out.printf("[%s]can't match!%n", name);
            }
        });
        System.out.println("SQL: \r\n " + builder.toString());
        System.out.println("SUCCESS: " + index[0]);
        workbook.close();
    }
}
